/*
 * Funambol is a mobile platform developed by Funambol, Inc. 
 * Copyright (C) 2008 Funambol, Inc.
 * 
 * This program is free software; you can redistribute it and/or modify it under
 * the terms of the GNU Affero General Public License version 3 as published by
 * the Free Software Foundation with the addition of the following permission 
 * added to Section 15 as permitted in Section 7(a): FOR ANY PART OF THE COVERED
 * WORK IN WHICH THE COPYRIGHT IS OWNED BY FUNAMBOL, FUNAMBOL DISCLAIMS THE 
 * WARRANTY OF NON INFRINGEMENT  OF THIRD PARTY RIGHTS.
 * 
 * This program is distributed in the hope that it will be useful, but WITHOUT
 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
 * FOR A PARTICULAR PURPOSE.  See the GNU General Public License for more
 * details.
 * 
 * You should have received a copy of the GNU Affero General Public License 
 * along with this program; if not, see http://www.gnu.org/licenses or write to
 * the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston,
 * MA 02110-1301 USA.
 * 
 * You can contact Funambol, Inc. headquarters at 643 Bair Island Road, Suite 
 * 305, Redwood City, CA 94063, USA, or at email address info@funambol.com.
 * 
 * The interactive user interfaces in modified source and object code versions
 * of this program must display Appropriate Legal Notices, as required under
 * Section 5 of the GNU Affero General Public License version 3.
 * 
 * In accordance with Section 7(b) of the GNU Affero General Public License
 * version 3, these Appropriate Legal Notices must retain the display of the
 * "Powered by Funambol" logo. If the display of the logo is not reasonably 
 * feasible for technical reasons, the Appropriate Legal Notices must display
 * the words "Powered by Funambol".
 */
#include "NoteDataStore.h"
#include "base/Log.h"

#define CREATE_FUN_NOTE_STATE   "CREATE TABLE IF NOT EXISTS fun_note_state(uid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE, \
                               note_id INTEGER, creation INTEGER, changed INTEGER, deleted INTEGER NOT NULL DEFAULT 0, \
                               updated_from_client INTEGER NOT NULL DEFAULT 0, sent_status INTEGER NOT NULL DEFAULT 0); "

#define TRIGGER_NOTE_NEW         "CREATE TRIGGER IF NOT EXISTS fun_insert_item_status AFTER INSERT ON Note \
                               BEGIN \
                               INSERT INTO fun_note_state(note_id, creation, changed, deleted) VALUES(NEW.ROWID, strftime('%s', 'now'), 0, 0); \
                               END;"

#define TRIGGER_NOTE_DELETE      "CREATE TRIGGER IF NOT EXISTS fun_delete_item_status AFTER DELETE ON Note \
                               BEGIN \
                               UPDATE fun_note_state SET deleted = strftime('%s', 'now') WHERE note_id = OLD.ROWID AND deleted = 0; \
                               UPDATE fun_note_state SET changed = 0 WHERE note_id = OLD.ROWID; \
                               END;"
#define TRIGGER_NOTE_UPDATE      "CREATE TRIGGER IF NOT EXISTS fun_update_item_status AFTER UPDATE ON Note \
                               BEGIN \
                               UPDATE fun_note_state SET changed = strftime('%s', 'now'), note_id = NEW.ROWID WHERE note_id = OLD.ROWID AND deleted = 0; \
                               END;"

int NoteDataStore::updateStatus(const char* rowid){

    StringBuffer query;
    query.sprintf("UPDATE fun_note_state SET updated_from_client = 1 WHERE note_id = %s;", rowid);
    int ret = driver.execute(query);
    return ret;
}

/**
 * * Make a select on the fun_note_state to understand if it exists. If the query fails,
 * * the table doesn't exit so the db is not initialized by the client.
 * * In this case, the plug-in creates the table and the needed triggers
 * * @return 0 if ok, a value if error.
 * */
int NoteDataStore::checkDBFunTable(const char* cpath) {
    LOG.debug("NoteDataStore: CheckDBFunTable");
    StringBuffer query("SELECT count(*) from fun_note_state;");
    ArrayList al;
    SQLiteDriver d(cpath);
    int ret = d.queryIndex(query, al);
    if (al.size() == 0) {
        ret = d.execute(CREATE_FUN_NOTE_STATE);
        if (ret != 0) {
            LOG.error("Fatal error: cannot create the fun_note_state table");
            ret = 1;
            goto finally;
        }                         // create the triggers
        d.commit();
        ret = d.execute(TRIGGER_NOTE_NEW);
        if (ret != 0) {
            ret = 2;
            LOG.error("Fatal error: cannot create the trigger for new notes on fun_note_state table");
            goto finally;
        }                  
        d.commit();
        ret = d.execute(TRIGGER_NOTE_UPDATE);
        if (ret != 0) {
            ret = 3;
            LOG.error("Fatal error: cannot create the trigger for updated notes on fun_note_state table");
            goto finally;
        }
        d.commit();
        ret = d.execute(TRIGGER_NOTE_DELETE);
        if (ret != 0) {
            ret = 4;
            LOG.error("Fatal error: cannot create the trigger for deleted notes on fun_note_state table");
            goto finally;
        }
        d.commit();
        query = "SELECT ROWID FROM Note;";
        ret = d.queryIndex(query, al);
        d.commit();
        for (int i = 0; i<al.size(); i++){
            query.sprintf("INSERT INTO fun_note_state (note_id, creation) VALUES (%s ,  %li);", ((StringBuffer*)(al.get(i)))->c_str(), time(NULL));
            d.execute(query.c_str());
            d.commit();
        }        
    }
    
    finally:
    // driver.disconnect(); ???       
    return ret;
}

int NoteDataStore::resetFlags(unsigned long lastsync){
    LOG.debug("NoteDataStore: resetFlags");
    StringBuffer query;
    query.sprintf("UPDATE fun_note_state SET creation = %li WHERE creation >= %li AND updated_from_client = 1 ;", lastsync, lastsync);
    LOG.debug("NoteDataStore resetFlags: %s", query.c_str());
    int ret = driver.execute(query);
    if (ret != 0){
        LOG.error("Error resetting flags in NoteDataStore");
        return ret;
    }
    ret = driver.commit();

    query.sprintf("UPDATE fun_note_state SET deleted = %li WHERE deleted >= %li AND updated_from_client = 1 ;", lastsync, lastsync);
    LOG.debug("NoteDataStore resetFlags: %s", query.c_str());
    ret = driver.execute(query);
    if (ret != 0){
        LOG.error("Error resetting flags in NoteDataStore");
        return ret;
    }
    ret = driver.commit();

    query.sprintf("UPDATE fun_note_state SET changed = %li WHERE changed >= %li AND updated_from_client = 1 ;", lastsync, lastsync);
    LOG.debug("NoteDataStore resetFlags: %s", query.c_str());
    ret = driver.execute(query);
    if (ret != 0){
        LOG.error("Error resetting flags in NoteDataStore");
        return ret;
    }
    ret = driver.commit();


    query ="UPDATE fun_note_state SET updated_from_client = 0;";
    LOG.debug("NoteDataStore resetFlags: %s", query.c_str());
    ret = driver.execute(query);
    if (ret != 0){
        LOG.error("Error resetting flags in NoteDataStore");
        return ret;
    }
    ret = driver.commit();
    return ret;
}

int NoteDataStore::addPIMItem(StringBuffer& identifier, const PIMItem& item){
    LOG.debug("NoteDataStore: addPIMItem");
    int ret;

    unsigned long t = time(NULL);
    NSDate* date = [NSDate  dateWithTimeIntervalSince1970:t];
    
    StringBuffer query;
    StringBuffer title = ((Note&)item).getTitle();
    StringBuffer body = ((Note&)item).getBody();
    title.replaceAll("'", "''");
    body.replaceAll("'", "''");
    query.sprintf("INSERT INTO Note(creation_date, title, summary, contains_cjk) VALUES(%lu,'%s','%s',0);",
                  ((unsigned long)[date timeIntervalSinceReferenceDate]),
                  title.c_str(), body.c_str());
    ret = driver.execute(query);
    if (ret != 0){
        return ret;
    }
    driver.commit();
    if (ret != 0){
        return ret;
    }
    [date release];
    ArrayList al;
    ret = driver.queryIndex("SELECT MAX(ROWID) FROM Note;", al );
    if (ret != 0){
        return ret;
    }
    identifier = ((StringBuffer*)(al.get(0)))->c_str();

    StringBuffer forbody = ((Note&)item).getFormattedBody();
    forbody.replaceAll("'", "''");
    query = "INSERT INTO note_bodies(note_id, data) VALUES('";
    query.append(identifier);
    query.append("','");
    query.append( forbody.c_str());
    query.append("');");
    ret = driver.execute(query);
    if (ret != 0){
        return ret;
    }
    ret = updateStatus(identifier);
    if (ret != 0){
        return ret;
    }
    driver.commit();
    return ret;
}

int NoteDataStore::deletePIMItem(const char* identifier){
    LOG.debug("NoteDataStore: deletePIMItem");
    int ret;

    StringBuffer query = "DELETE FROM Note WHERE ROWID =";
    query.append(identifier);
    query.append(";");
    
    ret = driver.execute(query);
    if (ret != 0){
        return ret;
    }
    ret = updateStatus(identifier);
    if (ret != 0){
        return ret;
    }
    ret = driver.commit();
    return ret;

}

int NoteDataStore::updatePIMItem(const char* identifier, const PIMItem& item){
    LOG.debug("NoteDataStore: updatePIMItem");
    int ret;
    StringBuffer title = ((Note&)item).getTitle();
    StringBuffer body = ((Note&)item).getBody();
    title.replaceAll("'", "''");
    body.replaceAll("'", "''");
    StringBuffer query = "UPDATE Note SET title = '";
    query.append(title.c_str());
    query.append("', summary='");
    query.append(body.c_str());
    query.append("' WHERE ROWID =");
    query.append(identifier);
    query.append(";");
    ret = driver.execute(query);
    if (ret != 0){
        return ret;
    }
    
    query.empty(); 
    StringBuffer forbody = ((Note&)item).getFormattedBody();
    forbody.replaceAll("'", "''");
    
    query = "UPDATE note_bodies SET data = '";
    query.append(forbody.c_str());
    query.append("' WHERE note_id =");
    query.append(identifier);
    query.append(";");
    ret = driver.execute(query);
    if (ret != 0){
        return ret;
    }
    ret = updateStatus(identifier);
    if (ret != 0){
        return ret;
    }
    ret = driver.commit();
    
    return ret;

}

PIMItem* NoteDataStore::getPIMItem(const char* identifier){ 
    LOG.debug("NoteDataStore: getPIMItem");
    Note* n = new Note();
    StringBuffer query = "SELECT title FROM Note WHERE ROWID =";
    query.append(identifier);
    query.append(";");
    ArrayList al;
    driver.queryIndex(query, al);
    StringBuffer title(((StringBuffer*)al.get(0))->c_str());
    n->setTitle(title.c_str());
    al.clear();

    query = "SELECT data FROM note_bodies WHERE note_id =";
    query.append(identifier);
    query.append(";");
    driver.queryIndex(query, al); 
    StringBuffer body = ((StringBuffer*)al.get(0))->c_str();
    body.replaceAll("<div><br class=\"webkit-block-placeholder\"></div>", "</div><div>");
    body.replaceAll("</div><div>", "\r\n");
    body.replaceAll("</div>", "");
    StringBuffer val = body.substr(title.length() + 5, body.length());
    
    n->setBody(val.c_str());
    al.clear();

    return (PIMItem*)n;
}
